
package revokeroleservice;

import java.io.*;
import java.sql.*;


public class RevokeRoleImpl implements RevokeRoleIF{
    
	
	
	
	/* (non-Javadoc)
	 * @see revokeroleservice.RevokeRoleIF#revokeRole(int, java.lang.String, java.lang.String)
	 */
	public boolean revokeRole(int comId, String roleName, String EPCClass)
	{
	
		
		//define the driver to use 
	      String driver = "org.apache.derby.jdbc.ClientDriver";
	    //the database name  
	      String dbName="RSSdb";

	    //define the Derby connection URL to use 
	      String connectionURL = "jdbc:derby://localhost:1527/" + dbName;
	      System.out.println("###the connectionURL is: "+connectionURL);
	    
	    
	      Connection conn = null;
	      Statement s;
	      Statement s1;
	      ResultSet rs;
	      
	      
	    //load derby driver
	      try{
	          Class.forName(driver); 
	          System.out.println(driver + " loaded. ");
	      } catch(java.lang.ClassNotFoundException e)     {
	          System.err.print("ClassNotFoundException: ");
	          System.err.println(e.getMessage());
	          System.out.println("\n    >>> Please check your CLASSPATH variable   <<<\n");
	      }
	      
		
		try{
			conn = DriverManager.getConnection(connectionURL);
			System.out.println("Connected to database " + dbName);
			
			s=conn.createStatement();
			
			//construct sql: get role grant record for the applicant
			String sqlGrantedRolePosition = "SELECT * FROM roleGrant WHERE roleName='"+roleName+"' AND EPCClass='"+EPCClass+"' AND lid="+comId;


			
			rs = s.executeQuery(sqlGrantedRolePosition);
			
			//set a flag to test whether the result is empty
			boolean empty=true;
			while(rs.next()){
				empty=false;
			}
			//check is the ResultSet is empty
			if(empty==true)
			{
				System.out.println("###There is no target role: "+roleName+" with EPCClass: "+EPCClass+" has been granted to Comany: "+comId);
				conn.close();
				return false;
			}else{
				//construct a sql to delete the role grant record
				String sqlDelete = "DELETE FROM roleGrant where roleName='"+roleName+"' AND EPCClass='"+EPCClass+"'";
				s1=conn.createStatement();
				int row = s1.executeUpdate(sqlDelete);
				System.out.println(row+" rows has been deleted");
				System.out.println("Target role: "+roleName+" with EPCClass: "+EPCClass+" has been deleted");
			}
	
			conn.close();
					
		}catch(Exception e){
			e.printStackTrace();
		}

		return true;
	}
}

